﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL {
    public class AfficheInformAffixFiletbDAO {

        /// <summary>
        /// 保存公告通知上传附件信息
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="filePath"></param>
        /// <param name="ai_id"></param>
        /// <returns></returns>
        public int saveAffixFileInfo(string fileName, string filePath, int ai_id) {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into AfficheInformAffixFiletb values (@fileName, @filePath, @ai_id)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@fileName", fileName));
                sqlCmd.Parameters.Add(new SqlParameter("@filePath", filePath));
                sqlCmd.Parameters.Add(new SqlParameter("@ai_id", ai_id));
                count = sqlCmd.ExecuteNonQuery();
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据附件编号删除附件信息
        /// </summary>
        /// <param name="afid"></param>
        /// <returns></returns>
        public int delAffixFileById(int afid) {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from AfficheInformAffixFiletb wher AIAffixFileId = @afid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@afid", afid));
                count = sqlCmd.ExecuteNonQuery();
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据公告通知编号查找此公告所有附件信息
        /// </summary>
        /// <param name="aiid"></param>
        /// <returns></returns>
        public List<AfficheInformAffixFiletb> findAllFileByAIid(int aiid) {
            List<AfficheInformAffixFiletb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from AfficheInformAffixFiletb where AfficheInformId = @aiid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@aiid", aiid));
                sqlDr = sqlCmd.ExecuteReader();
                if(sqlDr.HasRows) {
                    list = new List<AfficheInformAffixFiletb>();
                    while(sqlDr.Read()) {
                        AfficheInformAffixFiletb aiaf = new AfficheInformAffixFiletb();
                        aiaf.AIAffixFileId = (int) sqlDr["AIAffixFileId"];
                        aiaf.AIAffixFileName = (string) sqlDr["AIAffixFileName"];
                        aiaf.AIAffixFilePath = (string) sqlDr["AIAffixFilePath"];
                        list.Add(aiaf);
                    }
                }
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
